﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class sqlConnectionData
    {
        public static SqlConnection Hamketnoi()
        {

            SqlConnection connection = new SqlConnection(@"Data Source=NGUYENTHANG\SQLEXPRESS;Initial Catalog=QLBANSACH;Integrated Security=True");
            return connection;
        }
    }
    public class SACH_DAO
    {
        public static DataTable Hienthisach()
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("HIENTHISACH", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static void InsertSach(Sach_DTO sach)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("INSERTSACH", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = sach.IdSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = sach.IdtlSach;
            command.Parameters.Add("@C", SqlDbType.Int, 50).Value = sach.IdnxbSach;
            command.Parameters.Add("@D", SqlDbType.NVarChar, 50).Value = sach.TenSach;
            command.Parameters.Add("@E", SqlDbType.Int, 50).Value = sach.GiaSach;
            command.Parameters.Add("@F", SqlDbType.NVarChar, 50).Value = sach.Trangthai;
            command.Parameters.Add("@G", SqlDbType.Int, 50).Value = sach.SlSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void UpdateSach(Sach_DTO sach)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("UPDATESACH", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = sach.IdSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = sach.IdtlSach;
            command.Parameters.Add("@C", SqlDbType.Int, 50).Value = sach.IdnxbSach;
            command.Parameters.Add("@D", SqlDbType.NVarChar, 50).Value = sach.TenSach;
            command.Parameters.Add("@E", SqlDbType.Int, 50).Value = sach.GiaSach;
            command.Parameters.Add("@F", SqlDbType.NVarChar, 50).Value = sach.Trangthai;
            command.Parameters.Add("@G", SqlDbType.Int, 50).Value = sach.SlSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void DeleteSach(Sach_DTO sach)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("DELETESACH", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = sach.IdSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
    public class CTHDX_DAO
    {
        public static void InsertCTHDX(CTHDX_DTO cthdx)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("INSERTCTHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = cthdx.Stt;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = cthdx.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.Int, 50).Value = cthdx.IdSach;
            command.Parameters.Add("@D", SqlDbType.Int, 50).Value = cthdx.IdnvSach;
            command.Parameters.Add("@E", SqlDbType.DateTime, 50).Value = cthdx.TgxSach;
            command.Parameters.Add("@F", SqlDbType.Int, 50).Value = cthdx.SlxSach;
            command.Parameters.Add("@G", SqlDbType.Int, 50).Value = cthdx.GiaxSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void UpdateCTHDX(CTHDX_DTO cthdx)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("UPDATECTHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = cthdx.Stt;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = cthdx.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.Int, 50).Value = cthdx.IdSach;
            command.Parameters.Add("@D", SqlDbType.Int, 50).Value = cthdx.IdnvSach;
            command.Parameters.Add("@E", SqlDbType.DateTime, 50).Value = cthdx.TgxSach;
            command.Parameters.Add("@F", SqlDbType.Int, 50).Value = cthdx.SlxSach;
            command.Parameters.Add("@G", SqlDbType.Int, 50).Value = cthdx.GiaxSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static DataTable HienthiCTHDX()
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("HIENTHICTHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static DataTable Hienthi(int stt)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("SELECT * FROM CTHDX WHERE STT = " + stt, connection);
            command.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static void DeleteCTHDX(CTHDX_DTO cthdx)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("DELETECTHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = cthdx.Stt;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
    public class CTHDN_DAO
    {
        public static void InsertCTHDN(CTHDN_DTO cthdn)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("INSERTCTHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = cthdn.Stt;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = cthdn.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.Int, 50).Value = cthdn.IdSach;
            command.Parameters.Add("@D", SqlDbType.NVarChar, 50).Value = cthdn.IdnvSach;
            command.Parameters.Add("@E", SqlDbType.DateTime, 50).Value = cthdn.TgnSach;
            command.Parameters.Add("@F", SqlDbType.Int, 50).Value = cthdn.SlnSach;
            command.Parameters.Add("@G", SqlDbType.Int, 50).Value = cthdn.GianSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void UpdateCTHDN(CTHDN_DTO cthdn)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("UPDATECTHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = cthdn.Stt;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = cthdn.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.Int, 50).Value = cthdn.IdSach;
            command.Parameters.Add("@D", SqlDbType.NVarChar, 50).Value = cthdn.IdnvSach;
            command.Parameters.Add("@E", SqlDbType.DateTime, 50).Value = cthdn.TgnSach;
            command.Parameters.Add("@F", SqlDbType.Int, 50).Value = cthdn.SlnSach;
            command.Parameters.Add("@G", SqlDbType.Int, 50).Value = cthdn.GianSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void DeleteCTHDN(CTHDN_DTO cthdn)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("DELETECTHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = cthdn.Stt;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static DataTable HienthiCTHDN()
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("HIENTHICTHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static DataTable Hienthi(int stt)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("SELECT * FROM CTHDN WHERE STT = " + stt, connection);
            command.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
    }
    public class NV_DAO
    {
        public static DataTable Loggin(NV_DTO nv)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("LOGGIN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = nv.UsernamenvSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = nv.PasswordnvSach;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static DataTable Phanquyen(NV_DTO nv)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("PHANQUYEN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = nv.UsernamenvSach;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static void InsertNV(NV_DTO nv)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("INSERTNV", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = nv.IdnvSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = nv.TennvSach;
            command.Parameters.Add("@C", SqlDbType.NVarChar, 50).Value = nv.UsernamenvSach;
            command.Parameters.Add("@D", SqlDbType.NVarChar, 50).Value = nv.PasswordnvSach;
            command.Parameters.Add("@E", SqlDbType.Int, 50).Value = nv.IdquyennvSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void UpdateNV(NV_DTO nv)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("UPDATENV", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = nv.IdnvSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = nv.TennvSach;
            command.Parameters.Add("@C", SqlDbType.NVarChar, 50).Value = nv.UsernamenvSach;
            command.Parameters.Add("@D", SqlDbType.NVarChar, 50).Value = nv.PasswordnvSach;
            command.Parameters.Add("@E", SqlDbType.Int, 50).Value = nv.IdquyennvSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void DeleteNV(NV_DTO nv)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("DELETENV", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = nv.IdnvSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static DataTable HienthiNV()
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("HIENTHINV", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        public static DataTable SeachNV(NV_DTO nv)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SEACHNV";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = nv.IdnvSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = nv.TennvSach;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
    }
    public class TL_DA0
    {
        public static void InsertTL(TL_DTO tl)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("INSERTTL", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = tl.IdtlSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = tl.TentlSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void UpdateTL(TL_DTO tl)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("UPDATETL", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = tl.IdtlSach;
            command.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = tl.TentlSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void DeleteTL(TL_DTO tl)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("DELETETL", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = tl.IdtlSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static DataTable HienthiTL()
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("HIENTHITL1", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
    }
    public class HDN_DAO
    {
        public static void InsertHDN(HDN_DTO hdn)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("INSERTHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = hdn.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.DateTime, 50).Value = hdn.TgnSach;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = hdn.SldhSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void UpdateHDN(HDN_DTO hdn)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("UPDATEHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = hdn.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.DateTime, 50).Value = hdn.TgnSach;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = hdn.SldhSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void DeleteHDN(HDN_DTO hdn)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("DELETEHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = hdn.IdhdSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static DataTable HienthiHDN()
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("HIENTHIHDN", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
    }
    public class HDX_DAO
    {
        public static void InsertHDX(HDX_DTO hdx)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("INSERTHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = hdx.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.DateTime, 50).Value = hdx.TgxSach;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = hdx.SldhSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void UpdateHDX(HDX_DTO hdx)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("UPDATEHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = hdx.IdhdSach;
            command.Parameters.Add("@C", SqlDbType.DateTime, 50).Value = hdx.TgxSach;
            command.Parameters.Add("@B", SqlDbType.Int, 50).Value = hdx.SldhSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void DeleteHDX(HDX_DTO hdx)
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("DELETEHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@A", SqlDbType.Int, 50).Value = hdx.IdhdSach;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public static DataTable HienthiHDX()
        {
            SqlConnection connection = sqlConnectionData.Hamketnoi();
            SqlCommand command = new SqlCommand("HIENTHIHDX", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
    }
}
